package com.freejava.jdbctest;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCExample {

    // JDBC连接信息
    private static final String JDBC_URL = "jdbc:mysql://127.0.0.1:3306/test_springboot?characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
    private static final String JDBC_USERNAME = "root";
    private static final String JDBC_PASSWORD = "root";

    // 数据库表名
    private static final String TABLE_NAME = "users";

    // 插入数据
    public void insertUser(String name, int age) {
        // 1.获取连接
        // 2.使用Connection 预编译sql，生成预编译模版对象
        // 3.在模版对象中添加占位符的数据
        // 4.执行update or query 操作
        // 5.如果是query，那么第五步处理query返回的结果集
        // 对于上述的步骤来说，最核心的就是sql的逻辑以及占位符的数据（完整的sql语义）
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement("INSERT INTO " + TABLE_NAME + " (name, age) VALUES (?, ?)")) {

            stmt.setString(1, name);
            stmt.setInt(2, age);
            stmt.executeUpdate();
            System.out.println("Inserted user into the database.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新数据
    public void updateUser(int id, String name, int age) {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement("UPDATE " + TABLE_NAME + " SET name = ?, age = ? WHERE id = ?")) {

            stmt.setString(1, name);
            stmt.setInt(2, age);
            stmt.setInt(3, id);
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("User with ID " + id + " updated successfully.");
            } else {
                System.out.println("No user found with ID " + id + ".");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 按 ID 查询
    public void queryByID(int id) {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + TABLE_NAME + " WHERE id = ?")) {

            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("User found - ID: " + id + ", Name: " + name + ", Age: " + age);
            } else {
                System.out.println("No user found with ID " + id + ".");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 查询列表
    public List<User> queryList() {
        List<User> userList = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
             Statement stmt = conn.createStatement()) {

            ResultSet rs = stmt.executeQuery("SELECT * FROM " + TABLE_NAME);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                userList.add(new User(id, name, age));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    public static void main(String[] args) {
        JDBCExample jdbcExample = new JDBCExample();

        // 插入示例
        jdbcExample.insertUser("John", 30);

//        // 更新示例
//        jdbcExample.updateUser(1, "John Doe", 35);
//
//        // 按 ID 查询示例
//        jdbcExample.queryByID(1);
//
//        // 查询列表示例
//        List<User> userList = jdbcExample.queryList();
//        System.out.println("User List:");
//        for (User user : userList) {
//            System.out.println(user);
//        }
    }
}

class User {
    private int id;
    private String name;
    private int age;

    public User(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}
